package com.tucanstubs.android_wheresmystuff.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
 * Class to keep track of the table and column names of both tables
 * in Where's My stuff (Users and Items). Objects of this class help open the database
 * for writing to as well as create and update tables when necessary
 * @author Group 37
 * @version 2.0 4/12/2013
 */
public class DatabaseOperator extends SQLiteOpenHelper{
	private static final String DB_Name = "wheres_my_stuff.db";

	private static DatabaseOperator mInstance;
	
	public static  String PATH_TO_DB;
	public static final String ITEM_TABLE_NAME = "ITEMS";
	public static final String ITEM_ID = "_id";
	public static final String USER_ID = "user_id";
	public static final String NAME_COLUMN = "name";
	public static final String DESCRIPTION_COLUMN = "description";
	public static final String LOCATION_COLUMN = "location";
	public static final String MATCHED_COLUMN = "matched";
	public static final String DATE_COLUMN = "date_entered";
	public static final String REWARD_COLUMN = "reward";
	public static final String ITEMTYPE_COLUMN = "item_type";
	public static final String TYPE_ID = "type_id";
	
	private static final String ITEM_CREATE_QUERY = "CREATE TABLE IF NOT EXISTS "
			+ ITEM_TABLE_NAME + "(" + ITEM_ID + " INTEGER PRIMARY KEY, "
			+ USER_ID + " TEXT, "+ NAME_COLUMN + " TEXT, "
			+ DESCRIPTION_COLUMN + " TEXT, " + LOCATION_COLUMN
			+ " TEXT, " + MATCHED_COLUMN + " INTEGER, "
			+ DATE_COLUMN + " TEXT, " + REWARD_COLUMN + " REAL, "
			+ ITEMTYPE_COLUMN + " TEXT, " + TYPE_ID + " TEXT);";
	
	public static final String USER_TABLE_NAME = "USERS";
	public static final String USERNAME_COLUMN = "username";
	public static final String FIRSTNAME_COLUMN = "firstname";
	public static final String LASTNAME_COLUMN = "lastname";
	public static final String PASSWORD_COLUMN = "password";
	public static final String LOCKED_COLUMN = "locked";
	public static final String EMAIL_COLUMN = "email";
	public static final String LOCKCOUNT_COLUMN = "lock_count";
	public static final String ADMIN_COLUMN = "admin";

	private static final String USER_CREATE_QUERY = "CREATE TABLE IF NOT EXISTS "
			+ USER_TABLE_NAME + "(" + USERNAME_COLUMN + " TEXT PRIMARY_KEY, "
			+ FIRSTNAME_COLUMN + " TEXT, " + LASTNAME_COLUMN + " TEXT, "
			+ PASSWORD_COLUMN + " TEXT, " + LOCKED_COLUMN + " INTEGER, "
			+ EMAIL_COLUMN + " TEXT, " + LOCKCOUNT_COLUMN + " INTEGER, "
			+ ADMIN_COLUMN + " INTEGER);";
	/**
	 * Constructor for the SQLiteOpenHelper. Takens in the current Activity 
	 * running and calls the superclass' constructor with our Database's name 
	 * @param context The current running Activity
	 */
	private DatabaseOperator(Context context){
		super(context,DB_Name,null,1);
	}
	/**
	 * Singleton implementation. Creates a new instance of DatabaseOperator
	 * if the current instance is null. Ensures that only one instance of 
	 * DatabaseOperator is available at any given time.
	 * @param context
	 * @return The current instance of DatabaseOperator
	 */
	public static DatabaseOperator getInstance(Context context){
		if(mInstance == null)
			mInstance = new DatabaseOperator(context);
		return mInstance;
	}
	/**
	 * Called if the device currently has no Where's My Stuff database
	 * 
	 * @param db A writeable database
	 */
	public void onCreate(SQLiteDatabase db){
		db.execSQL(ITEM_CREATE_QUERY);
		db.execSQL(USER_CREATE_QUERY);
	}
	/**
	 * Called if the table is ever modified.
	 * 
	 * @param db A writeable database
	 * @param oldVersion The previous version of the database
	 * @param newVersion The new version of the database
	 */
	public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
		db.execSQL("DROP TABLE IF EXISTS " + ITEM_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
		onCreate(db);
	}
}
